package com.mbc.rmt.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.common.dao.DAO;
import com.mbc.common.param.CriteriaParams;
import com.mbc.rmt.data.CashReceivedDownloadLogDataList;
import com.mbc.rmt.entities.CashReceivedDownloadLog;

public class CashReceivedDownloadLogDAOImpls extends DAO<CashReceivedDownloadLog> implements
		CashReceivedDownloadLogDAO {

	public CashReceivedDownloadLogDAOImpls(Connection conn){
		this.conn = conn;
		this.table_name = "CashReceivedDownloadLog";
	}
	
	@Override
	public void create(CashReceivedDownloadLog entity)throws SQLException {
		query = "insert into " + table_name + "(BatchId, BranchId, FromReceiptNo, ToReceiptNo, Downloaded) values(?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getBatchId());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.setString(i++, entity.getFromReceiptNo());
		pstmt.setString(i++, entity.getToReceiptNo());
		pstmt.setBoolean(i++, entity.isDownloaded());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public CashReceivedDownloadLog find(long batchId)throws SQLException {
		CashReceivedDownloadLog l_entity = null;
		query = getSelectQuery() + " where BatchId = " + batchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_entity = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_entity;
	}

	@Override
	protected CashReceivedDownloadLog readRecord(ResultSet rs)
			throws SQLException {
		CashReceivedDownloadLog l_entity = new CashReceivedDownloadLog();
		l_entity.setBatchId(rs.getLong("BatchId"));
		l_entity.setBranchId(rs.getLong("BranchId"));
		l_entity.setFromReceiptNo(rs.getString("FromReceiptNo"));
		l_entity.setToReceiptNo(rs.getString("ToReceiptNo"));
		l_entity.setDownloaded(rs.getBoolean("Downloaded"));
		return l_entity;
	}

	@Override
	public void updateDownloadStatus(long batchId) throws SQLException {
		query = "update " + table_name + " set Downloaded = ? where BatchId = " + batchId;
		pstmt = conn.prepareStatement(query);
		pstmt.setBoolean(1, true);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public CashReceivedDownloadLogDataList findLog(CriteriaParams cri)
			throws SQLException {
		CashReceivedDownloadLogDataList l_DataList = new CashReceivedDownloadLogDataList();
		ArrayList<CashReceivedDownloadLog> l_entityList = new ArrayList<CashReceivedDownloadLog>();
		String l_cri = " and (FromReceiptNo like '%" + cri.getKeyword() + "%'" +
				" or ToReceiptNo like '%" + cri.getKeyword() + "%')" +
				" and BranchId = " + cri.getBranchId();
		query = "set rowcount " + cri.getSize() +
			" select * from (select row_number() over(order by BatchId desc) as [rowno], * from " + table_name +
			" where 1 = 1" + l_cri + ") " + table_name +
			" where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {	
			l_entityList.add(readRecord(rs));
		} 
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(BatchId) from " + table_name + " where 1 = 1" + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

}
